package com.ttpai.boot.lbs.dao;

import com.ttpai.boot.lbs.model.RoutePlanReportVO;
import com.ttpai.boot.lbs.model.assist.GroupByVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

@Component
public class RoutePlanReportDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 查询哪那些天已经获取过经纬度了，那些天没有，如果有的返回日期和个数
     */
    public List<GroupByVO> selectHaveLLDayGroupByAll() {
        String sql = "SELECT DATE(CHECK_TIME) `DATE`,COUNT(LNG) CNT,COUNT(*) CNT_ALL FROM _KAIL_ROUTE_PLAN_REPORT GROUP BY DATE(CHECK_TIME) ORDER BY CNT DESC";
        List<GroupByVO> query = jdbcTemplate.query(sql, new RowMapper<GroupByVO>() {
            public GroupByVO mapRow(ResultSet resultSet, int i) throws SQLException {
                GroupByVO groupByVO = new GroupByVO();
                groupByVO.setField(resultSet.getString("DATE"));
                groupByVO.setCount(resultSet.getInt("CNT"));
                groupByVO.setCountAll(resultSet.getInt("CNT_ALL"));
                return groupByVO;
            }
        });
        return query;
    }

    /**
     * 查询哪那些天已经获取过经纬度了，那些天没有，如果有的返回日期和个数
     */
    public List<RoutePlanReportVO> selectRoutePlanReportByDate(Date date) {
        String sql = "SELECT `ID`,`CHECK_TIME`,`CHECK_ADMIN_NAME`,`BRANCH_CITY_NAME`,`BRANCH_AREA_NAME`,`ADDRESS`,`LNG`,`LAT` " +
                " FROM _KAIL_ROUTE_PLAN_REPORT WHERE CHECK_TIME > DATE(?) AND CHECK_TIME < SUBDATE(DATE(?),-1) ORDER BY CHECK_TIME";

        List<RoutePlanReportVO> query = jdbcTemplate.query(sql, new RowMapper<RoutePlanReportVO>() {
            public RoutePlanReportVO mapRow(ResultSet resultSet, int i) throws SQLException {
                RoutePlanReportVO reportVO = new RoutePlanReportVO();
                reportVO.setId(resultSet.getLong("ID"));
                reportVO.setCheckTime(resultSet.getTimestamp("CHECK_TIME"));
                reportVO.setCheckAdminName(resultSet.getString("CHECK_ADMIN_NAME"));
                reportVO.setBranchCityName(resultSet.getString("BRANCH_CITY_NAME"));
                reportVO.setBranchAreaName(resultSet.getString("BRANCH_AREA_NAME"));
                reportVO.setAddress(resultSet.getString("ADDRESS"));
                reportVO.setLng(resultSet.getDouble("LNG"));
                reportVO.setLat(resultSet.getDouble("LAT"));
                return reportVO;
            }
        }, date, date);
        return query;
    }

    public List<RoutePlanReportVO> selectRoutePlanReport(RoutePlanReportVO vo) {
        List<Object> params = new ArrayList<Object>();
        StringBuilder sql = new StringBuilder("SELECT `ID`,`CHECK_TIME`,`CHECK_ADMIN_NAME`,`BRANCH_CITY_NAME`,`BRANCH_AREA_NAME`,`ADDRESS`,`LNG`,`LAT` FROM _KAIL_ROUTE_PLAN_REPORT WHERE 1=1");
        if (null != vo.getCheckTime()) {
            sql.append(" AND CHECK_TIME > DATE(?) AND CHECK_TIME < SUBDATE(DATE(?),-1)");
            params.add(vo.getCheckTime());
            params.add(vo.getCheckTime());
        }
        if (null != vo.getBranchCityName()) {
            sql.append(" AND BRANCH_CITY_NAME=?");
            params.add(vo.getBranchCityName());
        }
        sql.append(" ORDER BY CHECK_TIME");

        List<RoutePlanReportVO> query = jdbcTemplate.query(sql.toString(), new RowMapper<RoutePlanReportVO>() {
            public RoutePlanReportVO mapRow(ResultSet resultSet, int i) throws SQLException {
                RoutePlanReportVO reportVO = new RoutePlanReportVO();
                reportVO.setId(resultSet.getLong("ID"));
                reportVO.setCheckTime(resultSet.getTimestamp("CHECK_TIME"));
                reportVO.setCheckAdminName(resultSet.getString("CHECK_ADMIN_NAME"));
                reportVO.setBranchCityName(resultSet.getString("BRANCH_CITY_NAME"));
                reportVO.setBranchAreaName(resultSet.getString("BRANCH_AREA_NAME"));
                reportVO.setAddress(resultSet.getString("ADDRESS"));
                reportVO.setLng(resultSet.getDouble("LNG"));
                reportVO.setLat(resultSet.getDouble("LAT"));
                return reportVO;
            }
        }, params.toArray(new Object[params.size()]));
        return query;
    }

    public Integer updateRoutePlanReportNotNull(RoutePlanReportVO report) {
        if (null != report && null != report.getId()) {
            List<Object> params = new ArrayList<Object>();
            StringBuilder sql = new StringBuilder("UPDATE _KAIL_ROUTE_PLAN_REPORT SET ");
            if (null != report.getLng()) {
                sql.append("LNG=?,");
                params.add(report.getLng());
            }
            if (null != report.getLat()) {
                sql.append("LAT=?,");
                params.add(report.getLat());
            }
            sql.append("ID=? WHERE ID=?");
            params.add(report.getId());
            params.add(report.getId());
            return jdbcTemplate.update(sql.toString(), params.toArray(new Object[params.size()]));
        }
        return 0;
    }

    public void test() {
//        List<TestModel> query = jdbcTemplate.query(
//                "SELECT  AUCTION_ID,USER_ID FROM AUCTION_USERID LIMIT 10",
//                new net.paoding.rose.jade.rowmapper.BeanPropertyRowMapper(TestModel.class, false, false));

//        net.paoding.rose.jade.rowmapper.BeanPropertyRowMapper beanPropertyRowMapper = new net.paoding.rose.jade.rowmapper.BeanPropertyRowMapper(TestModel.class, false, false);
//
//        List<TestModel> query = jdbcTemplate.query(
//                "SELECT  AUCTION_ID,USER_ID FROM AUCTION_USERID LIMIT 10",
//                new org.springframework.jdbc.core.BeanPropertyRowMapper<>(TestModel.class));


//        for (TestModel testModel : query) {
//            System.out.println(testModel);
//        }


//        List<Map<String, Object>> query = jdbcTemplate.query(
//                "SELECT  AUCTION_ID,USER_ID FROM AUCTION_USERID LIMIT 10",
//                new ColumnMapRowMapper());
//        System.out.println(query);
//
//        List<String> query = jdbcTemplate.query(
//                "SELECT  AUCTION_ID FROM AUCTION_USERID LIMIT 10",
//                new SingleColumnRowMapper());
//        System.out.println(query);
    }

    public static void main(String[] args) {
        System.out.println(JdbcUtils.convertUnderscoreNameToPropertyName("sasd_123_sd_ee_23"));
        System.out.println(JdbcUtils.convertUnderscoreNameToPropertyName("sasd123_sd_ee_23"));


        System.out.println(underscoreName("sasd123SdEe23"));
    }

    protected static String underscoreName(String name) {
        if (!StringUtils.hasLength(name)) {
            return "";
        } else {
            StringBuilder result = new StringBuilder();
            result.append(lowerCaseName(name.substring(0, 1)));

            for (int i = 1; i < name.length(); ++i) {
                String s = name.substring(i, i + 1);
                String slc = lowerCaseName(s);
                if (!s.equals(slc)) {
                    result.append("_").append(slc);
                } else {
                    result.append(s);
                }
            }

            return result.toString();
        }
    }

    protected static String lowerCaseName(String name) {
        return name.toLowerCase(Locale.US);
    }

    public static String convertUnderscoreNameToPropertyName(String name) {
        StringBuilder result = new StringBuilder();
        boolean nextIsUpper = false;
        if(name != null && name.length() > 0) {
            if(name.length() > 1 && name.substring(1, 2).equals("_")) {
                result.append(name.substring(0, 1).toUpperCase());
            } else {
                result.append(name.substring(0, 1).toLowerCase());
            }

            for(int i = 1; i < name.length(); ++i) {
                String s = name.substring(i, i + 1);
                if(s.equals("_")) {
                    nextIsUpper = true;
                } else if(nextIsUpper) {
                    result.append(s.toUpperCase());
                    nextIsUpper = false;
                } else {
                    result.append(s.toLowerCase());
                }
            }
        }

        return result.toString();
    }

}